package 临时工具;

import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

@Data
public class ExportExcel implements Serializable {
    /**
     * 当前行游标
     */
    private Integer rowIndex=0;
    private Integer sheetIndex=0;

    private XSSFSheet cellStyleTemplate;

    Map<Integer,Integer> sheetRowIndex=new HashMap<>();

    /**
     * 样式map
     */
    private Map<String, XSSFCellStyle> cellStyleMap = new HashMap<String, XSSFCellStyle>();
    /**
     * 行高map
     */
    private Map<String , Short> rowHeightMap=new HashMap<String, Short>();
    /**
     * 当前的excel对象
     */
    private XSSFWorkbook thisWorkBook;
    /**
     * 当前的sheet对象
     */
    private XSSFSheet thisSheet;

    /**
     * 响应流
     */
    HttpServletResponse response;

    public ExportExcel(HttpServletResponse rsp){
        this.response=rsp;
        this.thisWorkBook=new XSSFWorkbook();
        this.thisSheet=switchSheet(0);
        sheetRowIndex.put(0,0);
        initCellStyle();
    }
    public ExportExcel(HttpServletResponse rsp, String fileName) throws UnsupportedEncodingException {
        this.response=rsp;
        setFileName(fileName);
        this.thisWorkBook=new XSSFWorkbook();
        this.thisSheet=switchSheet(0);
        sheetRowIndex.put(0,0);
        initCellStyle();
    }
    public ExportExcel(){
        this.thisWorkBook=new XSSFWorkbook();
        this.thisSheet=switchSheet(0);
        sheetRowIndex.put(0,0);
        initCellStyle();
    }
    public ExportExcel(XSSFWorkbook workbook){
        this.thisWorkBook=workbook;
        this.thisSheet=switchSheet(0);
        sheetRowIndex.put(0,0);
        initCellStyle();
    }
    /**
     * 创建默认样式模板
     */
    public void initCellStyle(){
        // 创建字体：宋体，14号
        Font titleFont = this.thisWorkBook.createFont();
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 14);
        titleFont.setBold(true);
        // 创建单元格样式
        XSSFCellStyle title = this.thisWorkBook.createCellStyle();
        title.setFont(titleFont);

        //字体加粗

        // 背景色设置为 #C0C0C0
        title.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        title.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 水平垂直居中
        title.setAlignment(HorizontalAlignment.CENTER);
        title.setVerticalAlignment(VerticalAlignment.CENTER);
        title.setWrapText(true); // 启用自动换行

        // 设置边框为黑色
        title.setBorderTop(BorderStyle.THIN);
        title.setBorderBottom(BorderStyle.THIN);
        title.setBorderLeft(BorderStyle.THIN);
        title.setBorderRight(BorderStyle.THIN);
        title.setTopBorderColor(IndexedColors.BLACK.getIndex());
        title.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        title.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        title.setRightBorderColor(IndexedColors.BLACK.getIndex());



        // 创建字体：宋体，14号
        Font textFont = this.thisWorkBook.createFont();
        textFont.setFontName("宋体");
        textFont.setFontHeightInPoints((short) 11);

        // 创建单元格样式
        XSSFCellStyle text = this.thisWorkBook.createCellStyle();
        text.setFont(textFont);

        // 水平垂直居中
        text.setAlignment(HorizontalAlignment.CENTER);
        text.setVerticalAlignment(VerticalAlignment.CENTER);
        text.setWrapText(true); // 启用自动换行

        // 设置边框为黑色
        text.setBorderTop(BorderStyle.THIN);
        text.setBorderBottom(BorderStyle.THIN);
        text.setBorderLeft(BorderStyle.THIN);
        text.setBorderRight(BorderStyle.THIN);
        text.setTopBorderColor(IndexedColors.BLACK.getIndex());
        text.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        text.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        text.setRightBorderColor(IndexedColors.BLACK.getIndex());

        cellStyleMap.put("title",title);
        cellStyleMap.put("text",text);
    }

    /**
     * 设置响应头
     * @param rsp
     */
    public void setResponse(HttpServletResponse rsp){
        this.response=rsp;
    }
    /**
     * 相对路径初始化样式模板
     * @param cellStyleTemplatePath
     * @return
     * @throws IOException
     */
    public boolean setCellStyleTemplate(String cellStyleTemplatePath) throws IOException {
        return setCellStyleTemplate(new XSSFWorkbook(Thread.currentThread().getContextClassLoader().getResourceAsStream(cellStyleTemplatePath)));
    }
    public boolean setCellStyleTemplate(XSSFWorkbook workbook) {
        return setCellStyleTemplate(workbook.getSheetAt(0));
    }
    public boolean setCellStyleTemplate(XSSFSheet sheet) {
        this.cellStyleTemplate = sheet;
        return true;
    }

    /**
     * 写出到流
     * @param out
     * @throws IOException
     */
    public void write(OutputStream out) throws IOException {
        this.thisWorkBook.write(out);
        out.flush();
    }

    /**
     * 写出到响应流
     *
     * @throws IOException
     */
    public void writeResponse() throws IOException {
        ServletOutputStream out = this.response.getOutputStream();
        this.thisWorkBook.write(out);
        out.flush();
        out.close();
    }


    /**
     * 游标加一行
     */
    public void addRowIndex(){
        this.rowIndex++;
    }

    /**
     * 游标加 addNumber 行
     * @param addNumber
     */
    public void addRowIndex(Integer addNumber){
        this.rowIndex=this.rowIndex+addNumber;
    }

    public boolean initCellStyleMap(int... rows){
        try {
            for (int row : rows) {
                initCellStyleMap(row);
            }
        } catch (Exception e) {
            return false;
        }
        return true;
    }
    /**
     * 初始化样式
     * @param styleName 样式名称
     * @param rowIndex 行坐标
     * @param celIndex 列坐标
     * @return
     */
    public boolean initCellStyleMap( String styleName, int rowIndex, int celIndex){
        XSSFCellStyle cellStyle =null;
        try {
            cellStyle=this.cellStyleTemplate.getRow(rowIndex).getCell(celIndex).getCellStyle();
        }catch (Exception e){
            return false;
        }
        if (Objects.nonNull(cellStyle)){
            XSSFCellStyle style = this.thisWorkBook.createCellStyle();
            style.cloneStyleFrom(cellStyle);
            cellStyleMap.put(styleName,style);
            rowHeightMap.put(styleName,this.cellStyleTemplate.getRow(rowIndex).getHeight());
            return true;
        }
        return false;
    }
    /**
     * 初始化样式
     * @param rowIndex 行坐标
     * @param celIndex 列坐标
     * @return
     */
    public boolean initCellStyleMap(int rowIndex, int celIndex){
        XSSFCellStyle cellStyle =null;
        XSSFCell oldCell = this.cellStyleTemplate.getRow(rowIndex).getCell(celIndex);
        try {
            cellStyle=oldCell.getCellStyle();
        }catch (Exception e){
            return false;
        }
        if (Objects.nonNull(cellStyle)){
            XSSFCellStyle style = this.thisWorkBook.createCellStyle();
            style.cloneStyleFrom(cellStyle);
            cellStyleMap.put(oldCell.getStringCellValue(),style);
            rowHeightMap.put(oldCell.getStringCellValue(),this.cellStyleTemplate.getRow(rowIndex).getHeight());
            return true;
        }
        return false;
    }
    public boolean initCellStyleMap(int rowIndex){
        return initCellStyleMap(rowIndex,0);
    }

    /**
     * 切换sheet
     * @param sheetIndex
     * @return
     */
    public synchronized XSSFSheet switchSheet( int sheetIndex) {
        int numberOfSheets = this.thisWorkBook.getNumberOfSheets();
        if (numberOfSheets<(sheetIndex+1)){
            for (;numberOfSheets<(sheetIndex+1);numberOfSheets++){
                this.thisWorkBook.createSheet("sheet"+(numberOfSheets+1));
            }
        }
        this.sheetRowIndex.put(this.sheetIndex,this.rowIndex);
        this.sheetIndex=sheetIndex;
        this.rowIndex=Objects.isNull(this.sheetRowIndex.get(this.sheetIndex))?0:this.sheetRowIndex.get(this.sheetIndex);
        this.thisSheet=this.thisWorkBook.getSheetAt(sheetIndex);
        return this.thisSheet;
    }

    /**
     * 根据坐标获取row
     *
     * @return
     */
    public Row getRowByCoordinate(int rowIndex) {
        Row row = this.thisSheet.getRow(rowIndex);
        if (row==null) {
            row= this.thisSheet.createRow(rowIndex);
        }
        return row;
    }
    public  void setValue( int colIndex, String value) {
        setValue(null,this.rowIndex,this.rowIndex,colIndex,colIndex,value);
    }
    /**
     * 设置合并单元格
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param value
     */
    public  void setValue(String cellStyleName, int firstRow, int lastRow, int firstCol, int lastCol,Short rowHeight, String value) {
        setValue(cellStyleName,  firstRow,  lastRow,  firstCol,  lastCol,  value);
        for (; firstRow <= lastRow; firstRow++) {
            this.thisSheet.getRow(firstRow).setHeight(rowHeight);
        }
    }
    /**
     * 设置合并单元格
     * @param firstCol
     * @param lastCol
     * @param value
     */
    public  void setValue(String cellStyleName, int firstCol, int lastCol, String value) {
        setValue(cellStyleName,this.rowIndex,this.rowIndex,firstCol,lastCol,value);
    }

    /**
     * 设置合并单元格
     * @param colIndex
     * @param value
     */
    public  void setValue(String cellStyleName, int colIndex, String value) {
        setValue(cellStyleName,this.rowIndex,this.rowIndex,colIndex,colIndex,value);
    }
    public  void setValue(String cellStyleName, int colIndex, Integer value) {
        if (Objects.isNull(value)){
            setValue(cellStyleName,this.rowIndex,this.rowIndex,colIndex,colIndex,"");
        }else{
            setValue(cellStyleName,this.rowIndex,this.rowIndex,colIndex,colIndex,value.toString());

        }

    }
    public  void createMergedRegionImgByte( int colIndex, byte[] bytes) {
        createMergedRegionImgByte(null,this.rowIndex,this.rowIndex,colIndex,colIndex,bytes);
    }
    public  void createMergedRegionImg( int colIndex, String path,Boolean imgFlag) {
        createMergedRegionImg(null,this.rowIndex,this.rowIndex,colIndex,colIndex,path,imgFlag);
    }
    public  void createMergedRegionImg(String cellStyleName, int colIndex, String path,Boolean imgFlag) {
        createMergedRegionImg(cellStyleName,this.rowIndex,this.rowIndex,colIndex,colIndex,path,imgFlag);
    }
    public  void createMergedRegionImg(String cellStyleName, int firstCol, int lastCol, String path,Boolean imgFlag) {
        createMergedRegionImg(cellStyleName,this.rowIndex,this.rowIndex,firstCol,lastCol,path,imgFlag);
    }
    /**
     * 设置合并单元格
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param value
     */
    public  void setValue(String cellStyleName, int firstRow, int lastRow, int firstCol, int lastCol, String value) {
        if (firstRow != lastRow || firstCol != lastCol) {
            setScopeCellStyle(cellStyleName,firstRow,lastRow,firstCol,lastCol);
            CellRangeAddress regionx = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            this.thisSheet.addMergedRegion(regionx);
        }
        getCellByCoordinate(cellStyleName, firstRow, firstCol).setCellValue(StringUtils.isNotBlank(value)?value:"");
    }
    public  void setValue( int firstRow, int lastRow, int firstCol, int lastCol, String value) {
        if (firstRow != lastRow || firstCol != lastCol) {
            setScopeCellStyle(null,firstRow,lastRow,firstCol,lastCol);
            CellRangeAddress regionx = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            this.thisSheet.addMergedRegion(regionx);
        }
        getCellByCoordinate(null, firstRow, firstCol).setCellValue(StringUtils.isNotBlank(value)?value:"");
    }

    /**
     * 设置合并单元格插入图片
     *

     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public  void createMergedRegionImg(String cellStyleName, int firstRow, int lastRow, int firstCol, int lastCol, String path,Boolean imgFlag) {
        if (imgFlag){
            try {
                //先合并单元格
                setValue( cellStyleName,  firstRow,  lastRow,  firstCol,  lastCol, StringUtils.EMPTY);
                if (StringUtils.isBlank(path)){
                    return;
                }
                if (lastCol-firstCol>=1){
                    firstCol+=(lastCol-firstCol)/2;
                }
                insertImage(path,firstRow,firstCol);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }else{
            setValue(cellStyleName,firstRow,lastRow,firstCol,lastCol,path);
        }
    }
    public  void createMergedRegionImgByte(String cellStyleName, int firstRow, int lastRow, int firstCol, int lastCol,  byte[] bytes) {

            try {
                //先合并单元格
                setValue( cellStyleName,  firstRow,  lastRow,  firstCol,  lastCol, StringUtils.EMPTY);

                if (lastCol-firstCol>=1){
                    firstCol+=(lastCol-firstCol)/2;
                }
                insertImageByte(bytes,firstRow,firstCol);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }

    }

    public void insertImage(String imageUrl,int row,int col) {
        try {
            URL url = new URL(imageUrl);
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(5000);
            InputStream inputStream = conn.getInputStream();

            byte[] bytes = IOUtils.toByteArray(inputStream);
            insertImageByte(bytes,row,col);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void insertImageByte( byte[] bytes,int row,int col) {
        try {
            if (bytes==null || bytes.length==0){
                return;
            }
            int pictureIdx = this.thisWorkBook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
            CreationHelper helper = this.thisWorkBook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();
            Drawing drawing = this.thisSheet.createDrawingPatriarch();
            anchor.setCol1(col);
            anchor.setRow1(row);
//            anchor.setCol2(col+1);
//            anchor.setRow2(row+1);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

            Picture pict = drawing.createPicture(anchor, pictureIdx);
            pict.resize(1.0);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 设置合并单元格

     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public  void mergedRegion(String cellStyleName, int firstRow, int lastRow, int firstCol, int lastCol) {
        if (firstRow != lastRow || firstCol != lastCol) {
            setScopeCellStyle(cellStyleName,firstRow,lastRow,firstCol,lastCol);
            CellRangeAddress regionx = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            this.thisSheet.addMergedRegion(regionx);
        }
    }

    /**
     * 给一个范围的单元格设置格式
     * @param cellStyleName
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public void setScopeCellStyle(String cellStyleName,  int firstRow, int lastRow, int firstCol, int lastCol){
        for (; firstRow <= lastRow; firstRow++) {
            Row row = getRowByCoordinate( firstRow);
            int firstCol1=firstCol;
            if (Objects.nonNull(this.rowHeightMap.get(cellStyleName))){
                row.setHeight(this.rowHeightMap.get(cellStyleName));
            }
            for (; firstCol1 <= lastCol; firstCol1++) {
                getCellByCoordinate(row,cellStyleName,firstCol1);
            }
        }
    }

    /**
     * 根据坐标获取cell
     *
     * @param rowIndex
     * @param cellIndex
     * @return
     */
    public Cell getCellByCoordinate(String cellStyleName, int rowIndex, int cellIndex) {
        return getCellByCoordinate(getRowByCoordinate(rowIndex),cellStyleName,cellIndex);
    }
    /**
     * 根据坐标获取cell
     *
     * @param cellIndex
     * @return
     */
    public Cell getCellByCoordinate(Row row ,String cellName, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell==null){
            cell=row.createCell(cellIndex);
        }
        if (this.cellStyleMap.get(cellName) !=null){
            cell.setCellStyle(this.cellStyleMap.get(cellName));
        }
        return cell;
    }

    public void setSheetName(String name) {
        this.thisWorkBook.setSheetName(this.sheetIndex,name);
    }

    /**
     * 设置自动行高
     */
    public void autoHeight(){
        XSSFRow row = this.thisSheet.getRow(this.rowIndex);
        row.getCTRow().setCustomHeight(false);
    }

    /**
     * 设置自动行高
     * @param rowIndex
     */
    public void autoHeight(int rowIndex){
        XSSFRow row = this.thisSheet.getRow(rowIndex);
        row.getCTRow().setCustomHeight(false);
    }

    /**
     * 以wps显示宽度来进行设置
     * @param columnIndex
     * @param width
     */
    public void setColumnWidthByWPS(int columnIndex, double width){
        this.thisSheet.setColumnWidth(columnIndex,(int)width*255);
    }

    /**
     * 设置响应为Xlsx
     * @param fileName 导出文件名
     * @throws UnsupportedEncodingException
     */
    public void setFileName(String fileName) throws UnsupportedEncodingException {
        this.response.setContentType("application/octet-stream");
        this.response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
        this.response.setHeader("Access-Control-Expose-Headers","*");
    }





    public XSSFCellStyle getCellStyle(int rowIndex, int cellIndex) {
        return this.thisSheet.getRow(rowIndex).getCell(cellIndex).getCellStyle();
    }

    public XSSFCellStyle cloneStyleFrom(String styleName, XSSFCellStyle cellStyle) {
        XSSFCellStyle newCellStyle = this.thisWorkBook.createCellStyle();
        newCellStyle.cloneStyleFrom(cellStyle);
        cellStyleMap.put(styleName, newCellStyle);
        return newCellStyle;
    }
    public void setHeightInPoints(Integer rowIndex,float  height){
        this.getRowByCoordinate(rowIndex).setHeightInPoints(height);
    }
    public void setHeightInPoints(float  height){
        this.getRowByCoordinate(this.rowIndex).setHeightInPoints(height);
    }

    /**
     * 给单元格画斜线
     * @param col1 起始单元格坐标(左上角)
     * @param row1 起始单元格坐标(左上角)
     * @param col2 结束单元格坐标(左上角)
     * @param row2 结束单元格坐标(左上角)
     */
    public void drawDiagonalIn(int col1,int row1,int col2,int row2){


        // 创建绘图对象
        XSSFDrawing drawing = this.thisSheet.createDrawingPatriarch();

        // 在单元格 A1 (0,0) 中从左上到右下画斜线
        XSSFClientAnchor anchor = new XSSFClientAnchor(
                0, 0,             // dx1, dy1 - 起点偏移（左上角）
                0, 0,                 // dx2, dy2 - 终点偏移（右下角）
                col1, row1,                // col1, row1 - 起始单元格坐标
                col2, row2                       // col2, row2 - 终止单元格坐标（不超过A1）
        );
//        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); // 不随单元格调整

        // 创建一条线
        XSSFSimpleShape shape = drawing.createSimpleShape(anchor);
        shape.setShapeType(ShapeTypes.LINE);
        shape.setLineWidth(1);
        shape.setLineStyleColor(0, 0, 0); // 黑色

    }

    /**
     * 强制在打开这个工作表时重新计算所有公式
     * @param value
     */
    public void setForceFormulaRecalculation(boolean value) {
        this.thisSheet.setForceFormulaRecalculation(value);
    }
    public void setForceFormulaRecalculation() {
        setForceFormulaRecalculation(true);
    }

    /**
     * 文本的行数
     */
    public void setRowHeight(int textLines ) {
        int i = this.thisSheet.getDefaultRowHeight() * textLines;
        this.thisSheet.getRow(this.rowIndex).setHeight((short) i);
    }
}
